package com.liferay.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.liferay.dao.ConnectDao;
import com.liferay.dao.ScheduleDao;
import com.liferay.model.ListSubjectOpen;

public class ScheduleDaoImpl implements ScheduleDao {

	private static final String GET_LIST_SUBJECT_OPEN = "SELECT DISTINCT student.firstName,student.middleName,student.lastName,nlu_subjectopen.idSubjectOpen,nlu_subject.nameSubject,teacher.firstName,teacher.middleName,teacher.lastName,nlu_room.nameRoom,nlu_lesson.rankOfLesson,nlu_subjectopen.weekdays,nlu_class.nameOfClass,nlu_yearofsemester.semester,nlu_yearofsemester.yearOfSemester,date_start,date_end "
			+ "FROM nlu_subjectopen "
			+ "INNER JOIN nlu_subject ON nlu_subject.idSubject = nlu_subjectopen.fkSubject "
			+ "INNER JOIN nlu_class ON nlu_class.idClass = nlu_subjectopen.fkClass "
			+ "INNER JOIN nlu_lesson ON nlu_lesson.idLesson = nlu_subjectopen.fkLesson "
			+ "INNER JOIN nlu_room ON nlu_room.idRoom = nlu_subjectopen.fkRoom "
			+ "INNER JOIN user_ as teacher ON teacher.userId = nlu_subjectopen.fkTeacher "
			+ "INNER JOIN nlu_faculty ON nlu_faculty.idFaculty = nlu_subject.fkFaculty "
			+ "INNER JOIN nlu_yearofsemester ON nlu_yearofsemester.id = nlu_subjectopen.fkYearOfSemester "
			+ "INNER JOIN nlu_subjectdetail ON nlu_subjectdetail.fkSubjectOpen = nlu_subjectopen.idSubjectOpen "
			+ "INNER JOIN role_ "
			+ "INNER JOIN user_ as student ON student.userId = nlu_subjectdetail.fkStudent "
			+ "INNER JOIN users_roles ON users_roles.userId = student.userId AND users_roles.roleId = role_.roleId "
			+ "WHERE nlu_yearofsemester.id = (SELECT MAX(id) FROM nlu_yearofsemester) AND role_.`name`=? AND student.emailAddress=? "
			+ "ORDER BY weekdays,yearOfSemester;";
	private Connection conn;
	private PreparedStatement pre;
	private ResultSet rs;
	private List<ListSubjectOpen> listSubjectOpen;
	
	@Override
	public List<ListSubjectOpen> getListSubjectOpen(String role, String email) {
		conn = ConnectDao.getConnection();
		listSubjectOpen = new ArrayList<ListSubjectOpen>();
		try {
			pre = conn.prepareStatement(GET_LIST_SUBJECT_OPEN);
			pre.setString(1, role);
			pre.setString(2, email);
			rs = pre.executeQuery();
			while(rs.next()){
				ListSubjectOpen subOpen = new ListSubjectOpen();
				subOpen.setNameStudent(rs.getString(3) + " " + rs.getString(2) + " " + rs.getString(1));
				subOpen.setIdSubjectOpen(rs.getLong(4));
				subOpen.setNameSubject(rs.getString(5));
				subOpen.setNameTeacher(rs.getString(8) + " " + rs.getString(7)
						+ " " + rs.getString(6));
				subOpen.setNameRoom(rs.getString(9));
				subOpen.setRankOfLesson(rs.getString(10));
				subOpen.setWeek(rs.getInt(11));
				subOpen.setNameOfClass(rs.getString(12));
				subOpen.setSemester(rs.getInt(13));
				subOpen.setYearOfSemester(rs.getString(14));
				subOpen.setDateStart(rs.getString(15));
				subOpen.setDateEnd(rs.getString(16));
				listSubjectOpen.add(subOpen);
			}
			pre.close();
			rs.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listSubjectOpen;
	}
	
	public static void main(String[] args) {
		ScheduleDaoImpl s = new ScheduleDaoImpl();
		System.out.println(s.getListSubjectOpen("role_student", "11130052@hcmuaf.edu.vn"));
	}
}
